package com.fl.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.fl.sys.SysConfig;

/**
 * @author liliang
 * 
 */
public class DbUtil {

	public static final Logger log = Logger.getLogger(DbUtil.class);
	/** oracle 10g driver */
	private final static String DEFAULT_JDBC_DRIVER = "org.sqlite.JDBC";
	/** 数据库连接******** */
	private static Connection conn;

	static {

		try {
			Class.forName(DEFAULT_JDBC_DRIVER).newInstance();
			conn = DriverManager.getConnection("jdbc:sqlite:"
					+ SysConfig.SQLITE_FILE_PATH);
		} catch (Exception e) {
			log.error("Sqlite db init error!!!" + e);
		}
	}

	public static List<Map<String, Object>> query(String sql)
			throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Map<String, Object>> result = null;
		try {
			result = new ArrayList<Map<String, Object>>();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(toMap(rs));
			}
			return result;
		} catch (SQLException e) {
			log.error(e);
			throw e;
		} finally {
			try {
				close(rs);
				close(stmt);
			} catch (SQLException e) {
				log.error(e);
				throw e;
			}
		}
	}

	public static List<Map<String, Object>> query(String sql, List<Object> param)
			throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Map<String, Object>> result = null;
		try {
			result = new ArrayList<Map<String, Object>>();
			stmt = conn.prepareStatement(sql);
			fillStatement(stmt, param);
			rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(toMap(rs));
			}
			return result;
		} catch (SQLException e) {
			log.error(e);
			throw e;
		} finally {
			try {
				close(rs);
				close(stmt);
			} catch (SQLException e) {
				log.error(e);
				throw e;
			}
		}
	}

	public static int exec(String sql) throws SQLException {
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			return stmt.executeUpdate();
		} catch (SQLException e) {
			log.error(e);
			throw e;
		} finally {
			try {
				close(stmt);
			} catch (SQLException e) {
				log.error(e);
				throw e;
			}
		}
	}

	public static int exex(String sql, List<Object> param) throws SQLException {
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			fillStatement(stmt, param);
			return stmt.executeUpdate();
		} catch (SQLException e) {
			log.error(e);
			throw e;
		} finally {
			try {
				close(stmt);
			} catch (SQLException e) {
				log.error(e);
				throw e;
			}
		}
	}

	private static Map<String, Object> toMap(ResultSet rs) throws SQLException {
		Map<String, Object> result = new HashMap<String, Object>();
		ResultSetMetaData rsmd = rs.getMetaData();
		int cols = rsmd.getColumnCount();

		for (int i = 1; i <= cols; i++) {
			result.put(rsmd.getColumnName(i).toUpperCase(), rs.getObject(i));
		}

		return result;
	}

	private static void fillStatement(PreparedStatement stmt, List<Object> param)
			throws SQLException {

		// check the parameter count, if we can

		ParameterMetaData pmd = stmt.getParameterMetaData();
		int stmtCount = pmd.getParameterCount();
		int paramsCount = param == null ? 0 : param.size();

		if (stmtCount != paramsCount) {
			throw new SQLException("Wrong number of parameters: expected "
					+ stmtCount + ", was given " + paramsCount);
		}

		// nothing to do here
		if (param == null) {
			return;
		}

		for (int i = 0; i < param.size(); i++) {
			if (param.get(i) != null) {
				stmt.setObject(i + 1, param.get(i));
			} else {
				// VARCHAR works with many drivers regardless
				// of the actual column type. Oddly, NULL and
				// OTHER don't work with Oracle's drivers.
				int sqlType = Types.VARCHAR;

				try {
					sqlType = pmd.getParameterType(i + 1);
				} catch (SQLException e) {
					// pmdKnownBroken = true;
				}

				stmt.setNull(i + 1, sqlType);
			}
		}
	}

	private static void close(ResultSet rs) throws SQLException {
		if (rs != null) {
			rs.close();
		}
	}

	private static void close(Statement stmt) throws SQLException {
		if (stmt != null) {
			stmt.close();
		}
	}
}
